package com.wpp;

import com.sun.org.apache.regexp.internal.RE;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*
 * 此类中有针对一张表的通用增删改查的方法
 * 此类中有针对所有表的通用增删改查的方法
 *
 *
 *
 *
 * */
import com.DAO.Mybook;

public class UpdateDeleteInsertTest {

    /*
     * 针对所有表的通用查询   如果javaBean属性名与数据库字段名不一致，则可以取别名解决
     * */
    public <T> List<T> queryTest2(Class<T> clazz, String sql, Object... args) throws Exception {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            //获取连接
            connection = JDBCUtils.getConnection();

            //预编译sql
            ps = connection.prepareStatement(sql);

            //填参数
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //获取结果集
            ResultSet resultSet = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();

            //获取结果集有多少列
            int columnCount = metaData.getColumnCount();

            //造一个list存放对象
            ArrayList<T> list = new ArrayList<T>();
            //填充查询出来的数据
            while (resultSet.next()) {
                //通过反射造一个DAO空参构造器
                T t = clazz.newInstance();

                for (int j = 0; j < columnCount; j++) {

                    //获取每个列的数据
                    Object columnValue = resultSet.getObject(j + 1);

                    //获取列的别名，metaData.getColumnLable()  推荐使用，如果没有取别名，那就是列名
                    //获取每个列的列名，但是必须保证javaBean和列名保持一次
                    String columnName = metaData.getColumnLabel(j + 1);

                    //通过反射赋值，指定属性
                    Field field = clazz.getDeclaredField(columnName);

                    //因为是私有属性，所以设置这个为true
                    field.setAccessible(true);
                    //给相应的对象中的属性设置值
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps);
        }

        return null;

    }

    @Test
    public void test() throws Exception {
        //通用删除操作
        //String sql = "delete from mybook where book_id=?";
        //update(sql,"1");

        //通用修改操作
        //String sql = "update  mybook set book_name = ? where book_id = ? ";
        //update(sql,"python","10");
        String sql = "select book_name,person_name,book_id from mybook";
        List<Mybook> mybooks = queryTest2(Mybook.class, sql);
        mybooks.forEach(System.out::println);
    }

    /*
     * 增删改通用操作
     * */
    public void update(String sql, Object... args) throws Exception {

        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            connection = JDBCUtils.getConnection();
            //2.预编译sql
            ps = connection.prepareStatement(sql);
            //3.设置参数
            for (int i = 0; i < args.length; i++) {

                ps.setObject(i + 1, args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps);
        }


    }


    /*
     * 针对一张表的简单查询
     * */
    public void queryTest() throws Exception {

        Connection connection = null;
        PreparedStatement ps = null;

        try {
            connection = JDBCUtils.getConnection();
            String sql = "select * from mybook";
            ps = connection.prepareStatement(sql);

            ResultSet resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
            int columnCount = metaData.getColumnCount();//获取结果集的列数
            System.out.println(columnCount);
            System.out.println(metaData);
            while (resultSet.next()) {
                int book_id = resultSet.getInt(1);
                String book_name = resultSet.getString(2);
                String person_name = resultSet.getString(3);
                String price = resultSet.getString(4);
                Time time = resultSet.getTime(5);
                String press = resultSet.getString(6);

                Mybook mybook = new Mybook(book_id, book_name, person_name, price, time, press);
                System.out.println(mybook);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps);
        }
    }


    /*
     * 针对一张表的通用查询   如果javaBean属性名与数据库字段名不一致，则可以取别名解决
     * */
    public void queryTest1(String sql, Object... args) throws Exception {
        Connection connection = null;
        PreparedStatement ps = null;

        try {
            //获取连接
            connection = JDBCUtils.getConnection();

            //预编译sql
            ps = connection.prepareStatement(sql);

            //填参数
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //获取结果集
            ResultSet resultSet = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData metaData = resultSet.getMetaData();

            //获取结果集有多少列
            int columnCount = metaData.getColumnCount();
            System.out.println("*********" + columnCount);
            //造一个DAO空参构造器
            Mybook mybook1 = new Mybook();

            //填充查询出来的数据
            while (resultSet.next()) {
                for (int j = 0; j < columnCount; j++) {
                    //获取每个列的数据
                    Object columnValue = resultSet.getObject(j + 1);

                    //获取列的别名，metaData.getColumnLable()  推荐使用，如果没有取别名，那就是列名
                    //获取每个列的列名，但是必须保证javaBean和列名保持一次
                    String columnName = metaData.getColumnLabel(j + 1);

                    //通过反射赋值，指定属性
                    Field field = Mybook.class.getDeclaredField(columnName);

                    //因为是私有属性，所以设置这个为true
                    field.setAccessible(true);
                    //给相应的对象中的属性设置值
                    field.set(mybook1, columnValue);
                }
                System.out.println("*********" + mybook1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps);
        }


    }
}
